
create function Kiem_tra_ton_tai_maTK ( @Ma_TK char(13))
returns int
as
begin
	declare @Ton_tai int
	declare @c cursor
	declare @Ma_tai_khoan char(13)
	set @Ton_tai = 0
	set @c = cursor for select MaTK from TAI_KHOAN
	open @c
	fetch next from @c into @Ma_tai_khoan
	while @@fetch_status = 0
	begin
		if (@Ma_tai_khoan = @Ma_TK)
			begin
				set @Ton_tai = 1
				return @Ton_tai
			end
		fetch next from @c into @Ma_tai_khoan
	end	
	close @c
	deallocate @c
	return @Ton_tai
end

create procedure Phat_sinh_tu_dong_ma_TK  @Ma_TK char(13) out
as
begin
	declare @Day_chuoi_phat_sinh char(13)
	declare @nam int
	declare @Chin_so_cuoi int
	DECLARE @Upper INT 
	DECLARE @Lower INT
	
	set @nam = year (getdate())
	set @Upper = 100000000
	set @Lower = 999999999
	set @Chin_so_cuoi = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
	set @Day_chuoi_phat_sinh = cast( @nam as char(4)) + cast (@Chin_so_cuoi as char (9))
	while (dbo.Kiem_tra_ton_tai_maTK (@Day_chuoi_phat_sinh) = 1)
	begin
		set @Chin_so_cuoi = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
		set @Day_chuoi_phat_sinh = cast( @nam as char(4)) + cast (@Chin_so_cuoi as char (9))
	end
	set @Ma_TK = @Day_chuoi_phat_sinh
	print @Ma_TK
end
declare @t char(13)
exec Phat_sinh_tu_dong_ma_TK   @t out
select @t

--drop procedure Phat_sinh_tu_dong_ma_TK